const Service = require('egg').Service
const dayjs = require('dayjs')

class AnalyzeService extends Service {
  async getColumnToCourseData() {
    const { ctx, app } = this
    // 统计每种栏目的课程数量
    const columnRes = await ctx.find('CourseColumn', {
      attributes: {
        include: [
          [
            app.model.literal('(SELECT COUNT(*) FROM mooc_course WHERE column.channel_id = mooc_course.channel_id)'),
            'count'
          ]
        ]
      }
    }, 'findAll')
    const columnData = []
    columnRes.forEach(item => {
      const { channelId, channelName, count } = item.dataValues
      columnData.push({ channelId, channelName, count })
    })
    columnData.sort((a, b) => b.count - a.count)
    return {
      columnData
    }
  }
  // 获取栏目对应课程数据
  async getCategoryToCourseData(channelId) {
    const { ctx, app } = this
    const categoryRes = await ctx.find('CourseCategory', {
      attributes: {
        include: [
          [
            app.model.literal('(SELECT COUNT(*) FROM mooc_course WHERE category.category_id = mooc_course.category_id)'),
            'count'
          ]
        ]
      },
      where: {
        channelId
      }
    }, 'findAll')
    const categoryData = []
    categoryRes.forEach(item => {
      const { channelId, categoryId, categoryName, count } = item.dataValues
      categoryData.push({ channelId, categoryId, categoryName, count })
    })
    categoryData.sort((a, b) => b.count - a.count)
    return {
      categoryData
    }
  }

  // 统计课程不同阶段评论数
  async getCourseComment(courseId) {
    const { ctx } = this
    const res = await ctx.find('CourseComment', {
      where: {
        courseId
      }
    }, 'findAll')
    if (res) {
      const grade = {
        '5分': 0,
        '3-4分': 0,
        '1-2分': 0
      }
      res.forEach(item => {
        const data = item.dataValues
        if (data.mark === 5) {
          grade['5分'] += 1
        } else if (data.mark >= 3 && data.mark <= 4) {
          grade['3-4分'] += 1
        } else {
          grade['1-2分'] += 1
        }
      })
      return { grade }
    }
  }

  // 统计课程类别下不同年份参课人数
  async getCategoryPerson(categoryId) {
    const { ctx } = this
    const res = await ctx.find('CourseTerminfo', {
      where: {
        categoryId
      }
    }, 'findAll')
    const categoryYear = {}
    res.forEach(item => {
      const data = item.dataValues
      const year = new Date(data.startTime).getFullYear()
      if (categoryYear[year]) {
        categoryYear[year] += item.enrollCount
      } else {
        categoryYear[year] = item.enrollCount || 0
      }
    })
    return { categoryYear }
  }

  async getTerminfoPerson(categoryId) {
    const { ctx } = this
    const res = await ctx.find('CourseTerminfo', {
      where: {
        categoryId
      }
    }, 'findAll')
    const terminfoData = {}
    const dict = {}
    res.forEach(item => {
      if (dict[item.courseId]) {
        dict[item.courseId]++
      } else {
        dict[item.courseId] = 1
      }
      if (terminfoData[dict[item.courseId]]) {
        terminfoData[dict[item.courseId]] += item.enrollCount
      } else {
        terminfoData[dict[item.courseId]] = item.enrollCount || 0
      }
    })
    return { terminfoData }
  }

  // 获取每周爬取数量
  async getWeekLog() {
    const { ctx, app } = this
    const { Op } = app.Sequelize
    const startTime = dayjs().subtract(7, 'day').format('YYYY-MM-DD')
    const endTime = dayjs().format('YYYY-MM-DD HH:mm:ss')
    const res = await ctx.find('Log', {
      where: {
        createTime: {
          [Op.between]: [startTime, endTime]
        }
      },
      attributes: ['count', 'createTime']
    }, 'findAll')
    if (res) {
      const dict = {}
      res.forEach(item => {
        const data = item.dataValues
        const day = dayjs(data.createTime).format('YYYY-MM-DD')
        if (dict[day]) {
          dict[day] += data.count
        } else {
          dict[day] = data.count
        }
      })
      return { weekData: dict }
    }
    return { weekData: {}}
  }

  // 获取指定课程期数数据
  async getTerminfoRange(categoryIds) {
    const { ctx } = this
    const { Op } = this.app.Sequelize
    const ids = categoryIds.split(',')
    const res = await ctx.find('CourseTerminfo', {
      where: {
        categoryId: {
          [Op.in]: ids
        }
      },
      attributes: ['categoryId', 'courseId', 'enrollCount']
    }, 'findAll')
    if (res) {
      return res.map(item => item.dataValues)
    }
    return []
  }

  // 将不同数据规划到不同分期
  async planStage(categoryIds) {
    const terminfos = await this.getTerminfoRange(categoryIds)
    const tmpIds = {}
    const stageObj = {}
    terminfos.forEach(item => {
      if (tmpIds[item.courseId]) {
        tmpIds[item.courseId]++
      } else {
        tmpIds[item.courseId] = 1
      }
      const index = tmpIds[item.courseId]
      if (stageObj[index]) {
        stageObj[index].push(item)
      } else {
        stageObj[index] = [item]
      }
    })
    return { stageObj }
  }
}

module.exports = AnalyzeService
